import openpyxl, os
from openpyxl.styles import NamedStyle, Font, Border, Side, PatternFill, Alignment
from openpyxl.utils import get_column_letter
from openpyxl.chart import Series, LineChart, Reference, PieChart, ProjectedPieChart

bd = Border(left=Side(border_style="thin",
                      color='FF001000'),
            right=Side(border_style="thin",
                       color='FF110000'),
            top=Side(border_style="thin",
                     color='FF110000'),
            bottom=Side(border_style="thin",
                        color='FF110000'),
            diagonal=Side(border_style=None,
                          color='FF000000'),
            diagonal_direction=0,
            outline=Side(border_style=None,
                         color='FF000000'),
            vertical=Side(border_style=None,
                          color='FF000000'),
            horizontal=Side(border_style=None,
                            color='FF110000')
            )


def format_table(sheet):
    for i in range(1, sheet.max_column + 1):
        sheet.cell(row=1, column=i).font = Font(bold=True)
        sheet.cell(row=1, column=i).alignment = Alignment(horizontal='center', vertical='center')
    for row in sheet.rows:
        for cell in row:
            cell.border = bd
            cell.font = Font(name=u'微软雅黑', size=10)


def fit_width(sheet, cols, widths):
    for i in range(1, sheet.max_column + 1):
        col = get_column_letter(i)
        sheet.column_dimensions[col].auto_size = True
    for col, width in zip(cols, widths):
        sheet.column_dimensions[col].width = width
        # if 'Sheet' in wb.worksheets:
        #     wb.remove(wb['Sheet'])


def insert_pie(sheet):
    chart = PieChart()
    labels = Reference(sheet, min_col=1, min_row=2, max_row=sheet.max_row)
    data = Reference(sheet, min_col=sheet.max_column, min_row=2, max_row=sheet.max_row)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(labels)
    chart.title = "急停占比分析"
    chart.height = 12
    # chart.pie_chart.ProjectedPieChart(splitType ='percent')
    location = "A{0}".format(sheet.max_row + 2)
    sheet.add_chart(chart, location)


def insert_line(sheet):
    chart = LineChart()
    chart.title = "前5位急停点对照图"
    chart.y_axis.title = "累计时间（秒）"
    chart.x_axis.title = "时间轴（每小时）"
    data = Reference(sheet, min_col=1, max_col=sheet.max_column - 1, min_row=2, max_row=6)
    chart.add_data(data, titles_from_data=True, from_rows=True)
    chart.smooth = True
    dates = Reference(sheet, min_col=2, max_col=sheet.max_column - 1, min_row=1)
    chart.set_categories(dates)
    chart.height = 12
    chart.width = 25
    location = "G{0}".format(sheet.max_row + 2)
    sheet.add_chart(chart, location)


if __name__ == '__main__':
    path = r"C:\Users\hyq\Desktop\汇总数据.xlsx"
    # fit_width(path, ['A'], [20])
    insert_pie(path)
    insert_line(path)
